Module modules.db

File name: db.py Author: Martin Jůda Python Version: 3.7 Description: Module with database connector and ORM mapper

"""
    File name: db.py
    Author: Martin Jůda
    Python Version: 3.7
    Description: Module with database connector and ORM mapper
"""

from datetime import datetime
import random
from sqlalchemy.pool import NullPool
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    DateTime,
    LargeBinary,
    ForeignKey,
    Table,
)
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.sql.expression import func

from modules.constants import (
    DB_CONNECTION_STRING,
    DEFAULT_SUBJECT_NAME,
)

Base = declarative_base()


class SubjectEntity(Base):
    """Subject database table definition"""

    __tablename__ = "subject"

    department_shortcut = Column(String, primary_key=True)
    name = Column(String(30))

    # 1 (subject): M(questions) relationship
    questions = relationship("QuestionEntity", uselist=True, back_populates="subject")


class QuestionEntity(Base):
    """Question database table definition"""

    __tablename__ = "question"

    id = Column(Integer, primary_key=True)
    question = Column(String(255), nullable=False)
    department_shortcut = Column(String, ForeignKey("subject.department_shortcut"))
    right_answer = Column(String(255), nullable=False)
    wrong_answer_1 = Column(String(255), nullable=False)
    wrong_answer_2 = Column(String(255), nullable=False)
    wrong_answer_3 = Column(String(255), nullable=False)
    picture = Column(LargeBinary)

    subject = relationship("SubjectEntity", uselist=False, back_populates="questions")


association_table = Table(
    "device_permission",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("device_id", Integer, ForeignKey("device.id")),
    Column("study_programme_id", Integer, ForeignKey("study_programme.id")),
)


class DeviceEntity(Base):
    """Device database table definition"""

    __tablename__ = "device"

    id = Column(Integer, primary_key=True)
    description = Column(String)

    logs = relationship("LogEntity", uselist=True, back_populates="device")
    study_programmes = relationship(
        "StudyProgrammeEntity", secondary=association_table, back_populates="devices"
    )


class LogEntity(Base):
    """Log database table definition"""

    __tablename__ = "log"

    id = Column(Integer, primary_key=True)
    datetime = Column(DateTime, nullable=False)
    device_id = Column(Integer, ForeignKey("device.id"), nullable=False)
    card = Column(String, nullable=False)

    device = relationship("DeviceEntity", uselist=False, back_populates="logs")


class StudyProgrammeEntity(Base):
    """Study programme table definition"""

    __tablename__ = "study_programme"

    id = Column(Integer, primary_key=True)
    study_code = Column(String, nullable=False)
    description = Column(String)

    devices = relationship(
        "DeviceEntity", secondary=association_table, back_populates="study_programmes"
    )


class Database:
    """Database connection and SQLAlchemy wrapper"""

    def __init__(self, db_url=None):
        if db_url is None:
            db_url = DB_CONNECTION_STRING
        self.engine = create_engine(db_url, poolclass=NullPool)
        self._create_tables()

    def _create_tables(self):
        Base.metadata.create_all(self.engine)

    def get_db_session(self):
        """Create database connection

        Returns:
            Database session, represents SQL transaction
        """
        session_maker = sessionmaker(bind=self.engine)
        return session_maker()

    @staticmethod
    def select_questions(db_session, subjects, requested_number_of_questions):
        """Select questions for chose subjects

        Args:
            db_session: Database connection
            subjects (list): list with subjects
            requested_number_of_questions (int): number of requested questions

        Returns:
            list(QuestionEntity): questions
        """
        questions = []
        for subject in subjects:
            # select subject entity
            subject_entity = (
                db_session.query(SubjectEntity)
                .filter(SubjectEntity.department_shortcut == subject["subject_code"])
                .first()
            )
            if subject_entity:
                number_of_questions = len(subject_entity.questions)
                if number_of_questions > 0:
                    # select random question for subject
                    index = random.choice(range(number_of_questions))
                    questions.append(subject_entity.questions[index])
        questions_count = len(questions)
        if questions_count < requested_number_of_questions:
            # select default questions if there is less questions for chose subjects
            default_questions = (
                db_session.query(QuestionEntity)
                .filter(QuestionEntity.department_shortcut == DEFAULT_SUBJECT_NAME)
                .order_by(func.random())
                .limit(requested_number_of_questions - questions_count)
            )
            questions.extend(default_questions)
        return questions

    @staticmethod
    def find_question_image(db_session, file_id):
        """Select picture by ID

        Args:
            db_session: Database connection
            file_id (int): ID of question

        Returns:
            QuestionEntity: entity with picture
        """
        file = (
            db_session.query(QuestionEntity.picture)
            .filter(QuestionEntity.id == file_id)
            .first()
        )
        if not file:
            raise LookupError()
        return file

    @staticmethod
    def device_by_id(db_session, device_id):
        """Check if device with given id exists in DB

        Args:
            db_session: Database connection
            device_id(int): device ID

        Returns:
            DeviceEntity: device entity
        """
        return (
            db_session.query(DeviceEntity).filter(DeviceEntity.id == device_id).first()
        )

    @staticmethod
    def store_access_log(db_session, card_number, device_id):
        log = LogEntity(
            device_id=device_id, card=card_number, datetime=datetime.utcnow()
        )
        db_session.add(log)
        db_session.commit()

Classes

class Database (db_url=None)

Database connection and SQLAlchemy wrapper

Expand source code

class Database:
    """Database connection and SQLAlchemy wrapper"""

    def __init__(self, db_url=None):
        if db_url is None:
            db_url = DB_CONNECTION_STRING
        self.engine = create_engine(db_url, poolclass=NullPool)
        self._create_tables()

    def _create_tables(self):
        Base.metadata.create_all(self.engine)

    def get_db_session(self):
        """Create database connection

        Returns:
            Database session, represents SQL transaction
        """
        session_maker = sessionmaker(bind=self.engine)
        return session_maker()

    @staticmethod
    def select_questions(db_session, subjects, requested_number_of_questions):
        """Select questions for chose subjects

        Args:
            db_session: Database connection
            subjects (list): list with subjects
            requested_number_of_questions (int): number of requested questions

        Returns:
            list(QuestionEntity): questions
        """
        questions = []
        for subject in subjects:
            # select subject entity
            subject_entity = (
                db_session.query(SubjectEntity)
                .filter(SubjectEntity.department_shortcut == subject["subject_code"])
                .first()
            )
            if subject_entity:
                number_of_questions = len(subject_entity.questions)
                if number_of_questions > 0:
                    # select random question for subject
                    index = random.choice(range(number_of_questions))
                    questions.append(subject_entity.questions[index])
        questions_count = len(questions)
        if questions_count < requested_number_of_questions:
            # select default questions if there is less questions for chose subjects
            default_questions = (
                db_session.query(QuestionEntity)
                .filter(QuestionEntity.department_shortcut == DEFAULT_SUBJECT_NAME)
                .order_by(func.random())
                .limit(requested_number_of_questions - questions_count)
            )
            questions.extend(default_questions)
        return questions

    @staticmethod
    def find_question_image(db_session, file_id):
        """Select picture by ID

        Args:
            db_session: Database connection
            file_id (int): ID of question

        Returns:
            QuestionEntity: entity with picture
        """
        file = (
            db_session.query(QuestionEntity.picture)
            .filter(QuestionEntity.id == file_id)
            .first()
        )
        if not file:
            raise LookupError()
        return file

    @staticmethod
    def device_by_id(db_session, device_id):
        """Check if device with given id exists in DB

        Args:
            db_session: Database connection
            device_id(int): device ID

        Returns:
            DeviceEntity: device entity
        """
        return (
            db_session.query(DeviceEntity).filter(DeviceEntity.id == device_id).first()
        )

    @staticmethod
    def store_access_log(db_session, card_number, device_id):
        log = LogEntity(
            device_id=device_id, card=card_number, datetime=datetime.utcnow()
        )
        db_session.add(log)
        db_session.commit()

Static methods

def device_by_id(db_session, device_id)

Check if device with given id exists in DB

Args

db_session

Database connection

device_id(int): device ID

Returns

DeviceEntity

device entity

Expand source code

@staticmethod
def device_by_id(db_session, device_id):
    """Check if device with given id exists in DB

    Args:
        db_session: Database connection
        device_id(int): device ID

    Returns:
        DeviceEntity: device entity
    """
    return (
        db_session.query(DeviceEntity).filter(DeviceEntity.id == device_id).first()
    )

def find_question_image(db_session, file_id)

Select picture by ID

Args

db_session

Database connection

file_id : int

ID of question

Returns

QuestionEntity

entity with picture

Expand source code

@staticmethod
def find_question_image(db_session, file_id):
    """Select picture by ID

    Args:
        db_session: Database connection
        file_id (int): ID of question

    Returns:
        QuestionEntity: entity with picture
    """
    file = (
        db_session.query(QuestionEntity.picture)
        .filter(QuestionEntity.id == file_id)
        .first()
    )
    if not file:
        raise LookupError()
    return file

def select_questions(db_session, subjects, requested_number_of_questions)

Select questions for chose subjects

Args

db_session

Database connection

subjects : list

list with subjects

requested_number_of_questions : int

number of requested questions

Returns

list(QuestionEntity): questions

Expand source code

@staticmethod
def select_questions(db_session, subjects, requested_number_of_questions):
    """Select questions for chose subjects

    Args:
        db_session: Database connection
        subjects (list): list with subjects
        requested_number_of_questions (int): number of requested questions

    Returns:
        list(QuestionEntity): questions
    """
    questions = []
    for subject in subjects:
        # select subject entity
        subject_entity = (
            db_session.query(SubjectEntity)
            .filter(SubjectEntity.department_shortcut == subject["subject_code"])
            .first()
        )
        if subject_entity:
            number_of_questions = len(subject_entity.questions)
            if number_of_questions > 0:
                # select random question for subject
                index = random.choice(range(number_of_questions))
                questions.append(subject_entity.questions[index])
    questions_count = len(questions)
    if questions_count < requested_number_of_questions:
        # select default questions if there is less questions for chose subjects
        default_questions = (
            db_session.query(QuestionEntity)
            .filter(QuestionEntity.department_shortcut == DEFAULT_SUBJECT_NAME)
            .order_by(func.random())
            .limit(requested_number_of_questions - questions_count)
        )
        questions.extend(default_questions)
    return questions

def store_access_log(db_session, card_number, device_id)

Expand source code

@staticmethod
def store_access_log(db_session, card_number, device_id):
    log = LogEntity(
        device_id=device_id, card=card_number, datetime=datetime.utcnow()
    )
    db_session.add(log)
    db_session.commit()

Methods

def get_db_session(self)

Create database connection

Returns

Database session, represents SQL transaction

Expand source code

def get_db_session(self):
    """Create database connection

    Returns:
        Database session, represents SQL transaction
    """
    session_maker = sessionmaker(bind=self.engine)
    return session_maker()

class DeviceEntity (**kwargs)

Device database table definition

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

Expand source code

class DeviceEntity(Base):
    """Device database table definition"""

    __tablename__ = "device"

    id = Column(Integer, primary_key=True)
    description = Column(String)

    logs = relationship("LogEntity", uselist=True, back_populates="device")
    study_programmes = relationship(
        "StudyProgrammeEntity", secondary=association_table, back_populates="devices"
    )

Ancestors

• sqlalchemy.ext.declarative.api.Base

Instance variables

var description

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var id

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var logs

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var study_programmes

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

class LogEntity (**kwargs)

Log database table definition

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

Expand source code

class LogEntity(Base):
    """Log database table definition"""

    __tablename__ = "log"

    id = Column(Integer, primary_key=True)
    datetime = Column(DateTime, nullable=False)
    device_id = Column(Integer, ForeignKey("device.id"), nullable=False)
    card = Column(String, nullable=False)

    device = relationship("DeviceEntity", uselist=False, back_populates="logs")

Ancestors

• sqlalchemy.ext.declarative.api.Base

Instance variables

var card

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var datetime

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var device

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var device_id

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var id

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

class QuestionEntity (**kwargs)

Question database table definition

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

Expand source code

class QuestionEntity(Base):
    """Question database table definition"""

    __tablename__ = "question"

    id = Column(Integer, primary_key=True)
    question = Column(String(255), nullable=False)
    department_shortcut = Column(String, ForeignKey("subject.department_shortcut"))
    right_answer = Column(String(255), nullable=False)
    wrong_answer_1 = Column(String(255), nullable=False)
    wrong_answer_2 = Column(String(255), nullable=False)
    wrong_answer_3 = Column(String(255), nullable=False)
    picture = Column(LargeBinary)

    subject = relationship("SubjectEntity", uselist=False, back_populates="questions")

Ancestors

• sqlalchemy.ext.declarative.api.Base

Instance variables

var department_shortcut

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var id

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var picture

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var question

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var right_answer

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var subject

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var wrong_answer_1

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var wrong_answer_2

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var wrong_answer_3

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

class StudyProgrammeEntity (**kwargs)

Study programme table definition

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

Expand source code

class StudyProgrammeEntity(Base):
    """Study programme table definition"""

    __tablename__ = "study_programme"

    id = Column(Integer, primary_key=True)
    study_code = Column(String, nullable=False)
    description = Column(String)

    devices = relationship(
        "DeviceEntity", secondary=association_table, back_populates="study_programmes"
    )

Ancestors

• sqlalchemy.ext.declarative.api.Base

Instance variables

var description

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var devices

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var id

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var study_code

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

class SubjectEntity (**kwargs)

Subject database table definition

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

Expand source code

class SubjectEntity(Base):
    """Subject database table definition"""

    __tablename__ = "subject"

    department_shortcut = Column(String, primary_key=True)
    name = Column(String(30))

    # 1 (subject): M(questions) relationship
    questions = relationship("QuestionEntity", uselist=True, back_populates="subject")

Ancestors

• sqlalchemy.ext.declarative.api.Base

Instance variables

var department_shortcut

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var name

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)

var questions

Expand source code

def __get__(self, instance, owner):
    if instance is None:
        return self

    dict_ = instance_dict(instance)
    if self._supports_population and self.key in dict_:
        return dict_[self.key]
    else:
        return self.impl.get(instance_state(instance), dict_)